# coding:utf-8
import pandas as pd
import datetime
import pymysql
import redis


class ReadExcel():

    def __init__(self):
        self.now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        HOST = 'oyo-scrapy-database.mysql.rds.aliyuncs.com'
        PORT = 3306
        USER = 'scrapy'
        PASSWD = 'Podf&T$2'
        DB = 'spider'
        CHARSET = 'utf8mb4'

        self.conn = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWD, db=DB, charset=CHARSET)
        self.cur = self.conn.cursor()

        RHOST = '10.200.20.200'
        RPORT = 6379
        RPASSWORD = 'zhangym930920'
        RDB = 1
        self.r = redis.Redis(host=RHOST, port=RPORT, password=RPASSWORD, db=RDB, charset='utf-8', decode_responses=True)

    def get_excel(self,date_,no):
        file_name='/ota_back_account/'+date_+no+'_otaaccount.xls'
        # file_name =date_ + no + '_otaaccount.xls'
        try:
            data = pd.read_excel(file_name)
        except Exception as err:
            print(err)
            print('未找到文件')
            return

        users=[]
        for index, row in data.iterrows():
            create_time = self.now
            batch_no = date_ + no
            # print(row)
            sync_account_id=row[6]
            sync_hotelname=row[7]

            sync_username_ctrip = row[8]
            sync_password_ctrip=row[9]
            ota_channel_en = 'ctrip'
            ctrip_flag=self.check(sync_username_ctrip,sync_password_ctrip)
            if ctrip_flag:
                # print(sync_account_id,sync_username_ctrip,sync_password_ctrip,ota_channel_en,create_time,batch_no)
                users.append((sync_account_id,sync_username_ctrip,sync_password_ctrip,ota_channel_en,create_time,batch_no))

            sync_username_meituan = row[10]
            sync_password_meituan = row[11]
            ota_channel_en = 'meituan'
            meituan_flag = self.check(sync_username_meituan, sync_password_meituan)
            if meituan_flag:
                # print(sync_account_id, sync_username_meituan, sync_password_meituan, ota_channel_en, create_time, batch_no)
                users.append((sync_account_id, sync_username_meituan, sync_password_meituan, ota_channel_en, create_time, batch_no))

            sync_username_fliggy = row[12]
            sync_password_fliggy = row[13]
            ota_channel_en = 'fliggy'
            fliggy_flag = self.check(sync_username_fliggy, sync_password_fliggy)
            if fliggy_flag:
                # print(sync_account_id, sync_username_fliggy, sync_password_fliggy, ota_channel_en, create_time, batch_no)
                users.append((sync_account_id, sync_username_fliggy, sync_password_fliggy, ota_channel_en, create_time, batch_no))

            sync_username_qunar = row[14]
            sync_password_qunar = row[15]
            ota_channel_en = 'qunar'
            qunar_flag = self.check(sync_username_qunar, sync_password_qunar)
            if qunar_flag:
                # print(sync_account_id, sync_username_qunar, sync_password_qunar, ota_channel_en, create_time, batch_no)
                users.append((sync_account_id, sync_username_qunar, sync_password_qunar, ota_channel_en, create_time, batch_no))

            sync_username_elong = row[16]
            sync_password_elong = row[17]
            ota_channel_en = 'elong'
            elong_flag = self.check(sync_username_elong, sync_password_elong)
            if elong_flag:
                # print(sync_account_id, sync_username_elong, sync_password_elong, ota_channel_en, create_time, batch_no)
                users.append((sync_account_id, sync_username_elong, sync_password_elong, ota_channel_en, create_time, batch_no))
        # print(users)

        for i in users:
            sync_account_id=i[0]
            sync_username=i[1]
            sync_password=i[2]
            ota_channel_en=i[3]
            create_time=i[4]
            batch_no=i[5]
            print(sync_account_id,sync_username,sync_password,ota_channel_en,create_time,batch_no)
            sql = "insert into ota_back_account_info (sync_account_id,sync_username,sync_password,ota_channel_en,create_time,batch_no) values ('%s','%s','%s','%s','%s','%s')" % (sync_account_id, sync_username, sync_password, ota_channel_en, create_time, batch_no)
            self.cur.execute(sql)
            self.conn.commit()

            # print(sync_account_id,sync_username,sync_password,sync_status,sync_group_account,sync_ota_channel_en,ota_channel_en,create_time,batch_no)
            # sql="insert into ota_back_account_info (sync_account_id,sync_username,sync_password,sync_status,sync_group_account,sync_ota_channel_en,ota_channel_en,create_time,batch_no) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s')"%(sync_account_id,sync_username,sync_password,sync_status,sync_group_account,sync_ota_channel_en,ota_channel_en,create_time,batch_no)
            # self.cur.execute(sql)
            # self.conn.commit()

    def check(self,username,password):
        flag=True
        if username =='(空)' or username =='' or username =='无' or username == '暂无OTA' or username == '/' or username == '未上线':
            flag=False
        if password =='(空)' or password =='':
            flag=False
        return flag

    def get_users(self,date_,no):
        # 携程
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='ctrip' and verify_time is null"%(date_+no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas,'ctrip')

        # 美团
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='meituan' and verify_time is null" % (date_ + no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas,'meituan')

        # 艺龙
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='elong' and verify_time is null" % (date_ + no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas, 'elong')

        # 去哪儿
        sql = "SELECT sync_username,sync_password,CRS_ID,batch_no from ota_back_account_info where batch_no='%s' and ota_channel_en='qunar' and verify_time is null" % (date_ + no)
        self.cur.execute(sql)
        datas = self.cur.fetchall()
        self.users_redis(datas, 'qunar')

    def users_redis(self,datas,ota_channel):
        for data in datas:
            Ctrip_Username = str(data[0]).strip().replace(' ','')
            Ctrip_Password = str(data[1]).strip().replace(' ','')
            CRS_ID = data[2]
            batch_no = data[3]

            print(Ctrip_Username, Ctrip_Password, CRS_ID, batch_no)
            self.r.rpush('hotel_users_'+ota_channel, (Ctrip_Username, Ctrip_Password, CRS_ID, batch_no))

if __name__ == '__main__':
    batch_no = datetime.datetime.now().strftime('%Y%m%d')
    no = datetime.datetime.now().strftime('%H')
    #no='15'
    begin=ReadExcel()
    begin.get_excel(batch_no, no)
    begin.get_users(batch_no, no)